| springboot 实现后端接口操作Excel的导出、批量导入功能 | 您所在的位置：网站首页 › springboot 导入导出 › springboot 实现后端接口操作Excel的导出、批量导入功能 |
springboot 实现后端接口操作Excel的导出、批量导入功能
|
本文操作Excel使用的是poi方式 pom中引用需要的jar包 org.apache.poi poi RELEASE org.apache.poi poi-ooxml RELEASE ExcelUtil工具类 package com.xj.demo.common; import com.xj.demo.model.Request.ExcelData; import lombok.extern.slf4j.Slf4j; import org.apache.poi.hssf.usermodel.*; import org.apache.poi.ss.usermodel.*; import javax.servlet.http.HttpServletResponse; import java.io.BufferedOutputStream; import java.io.FileInputStream; import java.io.InputStream; import java.io.OutputStream; import java.util.ArrayList; import java.util.List; import static org.apache.poi.ss.usermodel.CellType.*; /** * Excel 工具类 * **/ @Slf4j public class ExcelUtil { /** * 方法名：exportExcel * 功能：导出Excel */ public static void exportExcel(HttpServletResponse response, ExcelData data) { log.info("导出解析开始，fileName:{}",data.getFileName()); try { //实例化HSSFWorkbook HSSFWorkbook workbook = new HSSFWorkbook(); //创建一个Excel表单，参数为sheet的名字 HSSFSheet sheet = workbook.createSheet("sheet"); //设置表头 setTitle(workbook, sheet, data.getHead()); //设置单元格并赋值 setData(sheet, data.getData()); //设置浏览器下载 setBrowser(response, workbook, data.getFileName()); log.info("导出解析成功!"); } catch (Exception e) { log.info("导出解析失败!"); e.printStackTrace(); } } /** * setTitle设置标题 * */ private static void setTitle(HSSFWorkbook workbook, HSSFSheet sheet, String[] str) { try { HSSFRow row = sheet.createRow(0); //设置列宽，setColumnWidth的第二个参数要乘以256，这个参数的单位是1/256个字符宽度 for (int i = 0; i insert into user_info (id,uname,uage,create_time) values (#{user.id,jdbcType=VARCHAR}, #{user.uname,jdbcType=VARCHAR}, #{user.uage,jdbcType=INTEGER}, #{user.create_time,jdbcType=TIMESTAMP}) Service类 @Override public int addBatchUser(List users) { return uMapper.addBatchUser(users); } Controller类 @ApiOperation("导入用户信息") @PostMapping("/inportuserinfo") public Result InportUserInfo(@RequestParam("file") MultipartFile multipartFile) throws Exception { try { logger.info("user/inportuserinfo："); String[] extObjs={".xls",".xlsx"}; String fileName = multipartFile.getOriginalFilename(); String ext = FileUtil.getFileExt(fileName); if (!Arrays.asList(extObjs).contains(ext)) return Result.FAIL("文件为空或文件格式不正确!"); String path = FileUtil.uploadFile(multipartFile); List objs = ExcelUtil.importExcel(path); List list = new ArrayList(); if (CollectionUtils.isEmpty(objs)) return Result.FAIL(); for (int i = 0; i < objs.size(); i++) { User_info user = new User_info(); Object[] obj = objs.get(i); user.setId(UUID.randomUUID().toString()); user.setUname(String.valueOf(obj[0])); user.setUage(Integer.parseInt(String.valueOf(obj[1]))); user.setCreate_time(new Date()); list.add(user); } int result = userService.addBatchUser(list); if (result > 0) return Result.SUCCESS(); return Result.FAIL(); } catch (Exception ex) { logger.error("user/inportuserinfo：" + ex.getMessage()); return Result.FAIL(ex.getMessage()); } } 完整目录结构和 下载地址：https://download.csdn.net/download/haojuntu/13583314
|
【本文地址】
公司简介
联系我们